package com.mall.manager.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.mall.common.dao.MongoDBManager;
import com.mall.untils.Page;
import com.mongodb.DBObject;

@Service
public class GardenService {
	Logger logger=LoggerFactory.getLogger(GardenService.class);
	@Resource
	private JdbcTemplate jdbcTemplate;
	
	
	
	public String addData(){
		
		List<DBObject> findAll = MongoDBManager.findAll("php_school"); //查询所有园所
		Map<String, Object> map=null;
		for (DBObject dbObject : findAll) {
			map = new HashMap<String, Object>();
			Object schoolId = dbObject.get("id"); //获取园所id
			map.put("school_id", Long.valueOf(schoolId.toString()));
			List<DBObject> find = MongoDBManager.find("php_baby", map);//查出学生总数
			List<DBObject> find2 = MongoDBManager.find("php_class", map);//查出班级总数
			String sql = "insert into t_garden(garden_id,garden_name,garden_type,teacher_sum,student_sum,use_card,stuplus_card,create_time,update_time) values(?,?,?,?,?,?,?,now(),now())";
			jdbcTemplate.update(sql,dbObject.get("id"),dbObject.get("name"),0,find2==null?0:find2.size(),find==null?0:find.size(),1000,1000);
		}
		
		
		
		Map<String, Object> map1=null;
		String upsqlString="";
		//查询学生答题总数
		String sqlfind = "select baby_id,count(*) count from evaluation.reviews_score re where type ='总分' GROUP BY baby_id";
		List<Map<String,Object>> queryForList = jdbcTemplate.queryForList(sqlfind);
		
		for (Map<String, Object> map2 : queryForList) {
			Object baby_id = map2.get("baby_id");//获取答题的学生id
			map1 = new HashMap<String, Object>();
			map1.put("baby_id", Long.valueOf(baby_id.toString()));
			DBObject findOne = MongoDBManager.findOne("php_baby", map1);//查询学生信息
			
			//如果学生存在
			if(findOne !=null){
				Object school_id = findOne.get("school_id"); //获取学生所在园所id
				upsqlString="update t_garden set use_card=?,stuplus_card=? where garden_id=? "; //赋值点卡
				jdbcTemplate.update(upsqlString,map2.get("count").toString(),(1000-Integer.parseInt(map2.get("count").toString())),school_id);
			}
			
			
		}
		
		
		return "";
	}
	
	

	public Page queryPage(Map<String, Object> paraMap) {
		Page page = null;
		List<Map<String, Object>> queryForList = null;
		int pageNo = MapUtils.getIntValue(paraMap, "pageNo");
		String name = MapUtils.getString(paraMap, "name");  //TODO 提供园所名称
		String id = MapUtils.getString(paraMap, "id");  
		String garden_type = MapUtils.getString(paraMap, "garden_type");//园所类型
		int pageSize = 10; //每页显示10条
		Integer total=0; //总数
		try {
			if (pageNo == 0) {
				pageNo = 1;
			}
			pageNo = 2;
			StringBuffer sqlCount = new StringBuffer("select count(*) from t_garden where 1=1 ");
			
			if (StringUtils.isNotBlank(name)) {
				sqlCount.append(" and name like ? ");
			}
			
			if (StringUtils.isNotBlank(id)) {
				sqlCount.append(" and id = ? ");
			}
			
			if(StringUtils.isNotBlank(garden_type)){
				sqlCount.append(" and garden_type = ? ");
			}
			
			
			if(StringUtils.isBlank(name) && StringUtils.isBlank(garden_type)){
				total = jdbcTemplate.queryForObject(sqlCount.toString(), Integer.class);
			}
			if(StringUtils.isBlank(name) && StringUtils.isNotBlank(garden_type)){
				total = jdbcTemplate.queryForObject(sqlCount.toString(), Integer.class,garden_type);
			}
			if(StringUtils.isNotBlank(name) && StringUtils.isBlank(garden_type)){
				total = jdbcTemplate.queryForObject(sqlCount.toString(), Integer.class,name);
			}
			if(StringUtils.isNotBlank(name) && StringUtils.isNotBlank(garden_type)){
				total = jdbcTemplate.queryForObject(sqlCount.toString(), Integer.class,name,garden_type);
			}
			
			
			
			int startIndex = Page.getStartOfPage(pageNo, pageSize);
			StringBuffer sql = new StringBuffer("select * from t_garden where 1=1 ");
			if (StringUtils.isNotEmpty(name)) {
				sql.append("and name like ? ");
			}
			if (StringUtils.isNotEmpty(id)) {
				sqlCount.append(" and id = ? ");
			}
			if(StringUtils.isNotBlank(garden_type)){
				sqlCount.append(" and garden_type = ? ");
			}
			
			String sqlbf = sql.append(" order by id desc limit " + startIndex + "," + pageSize).toString();
			
			if (StringUtils.isNotEmpty(name)) {
				queryForList = jdbcTemplate.queryForList(sqlbf, "%"+name+"%");
			}
			if (StringUtils.isNotEmpty(id)) {
				queryForList = jdbcTemplate.queryForList(sqlbf, id);
			}
			if (StringUtils.isNotEmpty(id)) {
				queryForList = jdbcTemplate.queryForList(sqlbf, garden_type);
			}
			
			queryForList = jdbcTemplate.queryForList(sqlbf);
			page = new Page(startIndex, total, pageSize, queryForList);
			return page;
		} catch (Exception e) {
			e.printStackTrace();
			logger.info(e.toString());
			return null;
		}
	}

}
